-- 车辆信息
with cars as (
    select t1.*,t2.copilot_driver,copilot_tel from
    (
    select shipment_no
         , plate_number          as truck_number           -- 车牌号
         , trailer_number                                  -- 挂箱
         , max(vehicletype_id)   as vehicletype_id         --车型
         , max(vehicletype_name) as vehicletype_name       --车型
         , max(vehicle_volume)   as max_volume             --车辆荷载体积
         , max(load_weight)      as max_weight             --车辆荷载重量
         , max(carrier_id)       as carrier_id             --承运商ID
         , max(carrier_name)     as carrier_name           --承运商全名
         , max(driver_name)      as driver_name            --主驾驶员
         , max(driver_contact)   as driver1_tel            --主驾驶员电话
         , ''                    as actual_start_date_type --发车时间类型
         , ''                    as actual_end_date_type   --到车时间类型
         , ''                    as oil                    --油耗
         , ''                    as trailer_car_type       --挂车车厢类型
         , ''                    as center_one_disp_date   --网点一派时间
         , ''                    as center_two_disp_date   --网点二派时间
         , ''                    as center_three_disp_date --网点三派时间
    from jms_dwd.dwd_wide_tms_road_waybill_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-7) and  '{{ execution_date | cst_ds }}'
      and trailer_number is not null
    group by shipment_no, plate_number, trailer_number
) t1 left join jms_dim.dim_tmsnew_vehicle t2
    on t1.truck_number = t2.plate_number
),

     net as (
       select code,name
         from jms_dim.dim_network_whole_massage
         where network_type = 4
     ),

     -- 任务信息
     shipment as (
    select
           shipment_no
           ,plate_number
           ,trailer_number
           ,max(shipment_state) as shipment_state
           ,max(mileage) as mileage
           ,max(vehicleline_type) as vehicleline_type  -- 支线or干线
            -- 任务开始站点
            ,max(start_site_code) as shipment_start_code
            ,max(start_site_name) as shipment_start_name
            ,max(planned_departure_time) as shipment_plan_start_time -- 任务计划开始时间
            ,max(actual_departure_time) as shipment_departure_start_time -- 任务实际开始时间
            ,max(if(start_site_code == start_code,gps_departure_time,null)) as shipment_gps_departure_time -- 任务在起始网点的gps开始时间
            -- 任务目的站点
            ,max(end_site_code) as shipment_end_site_code -- end_site_name
            ,max(end_site_name) as shipment_end_site_name
            ,max(planned_arrival_time) as shipment_plan_end_time -- 任务计划到车时间
            ,max(actual_arrival_time) as shipment_departure_end_time -- 任务实际到车时间
            ,max(if(end_site_code==end_code,gps_arrival_time,'')) as shipment_gps_arrival_time --目的网点的gps到车时间

    from jms_dwd.dwd_wide_tms_road_waybill_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-7) and '{{ execution_date | cst_ds }}'
    and trailer_number is not null
    group by shipment_no, plate_number, trailer_number
),

     stop as (
         select *,
                row_number()
                    over (partition by shipment_no
                        order by start_site_sort_num) as roadsection_rank
         from (
                  select shipment_id
                       , shipment_no
                       , shipment_name
                       , plate_number
                       , trailer_number
                       , roadsection_id                                                                                            -- 路段编号
                       , max(start_site_sort_num) as start_site_sort_num
                       -- ,max(handling_type) as handling_type -- 装卸类型
                       , max(load_start_time)     as load_start_time  -- 装箱开始时间
                       , max(load_end_time)       as load_end_time    --装箱结束时间
                       -- ,max(run_type) as run_type      -- 作业类型
                       --,null as run_type
                       , max(road_mileage)                                                     as road_maleage                     -- 规划里程
                       , if(count(if(start_site_code is not null, billcode, null)) > 0, 0, 1)  as is_emport
                       , max(business_attribute)                                               as is_over_time
                       ,max(if(substr(shipment_no,1,2)='WF',1,0)) as return_sign
                       -- 装载率
                       , cast(sum(package_total_weight) * 100 / max(load_weight) as double)    as loadrate_weight
                       , cast(sum(package_total_volume) * 100 / max(vehicle_volume) as double) as loadrate_volume
                       , max(planned_departure_time)                                           as load_site_planned_departure_time -- 计划发车时间
                       , max(actual_departure_time)                                            as load_site_actual_departure_time  -- 实际发车时间
                       , max(planned_arrival_time)                                             as unload_site_planned_arrival_time -- 计划到车时间
                       , max(actual_departure_time)                                            as unload_site_app_arrival_time     -- 实际到车时间
                       , max(plan_driving_time)                                                as plan_driving_time                -- 计划行驶时间
                       , max(actual_driving_time)                                              as actual_driving_time              -- 实际行驶时间
                       , min(load_start_time)                                                  as load_site_scantime               -- 装车扫描时间
                       , min(app_departure_time)                                               as load_site_app_departure_time     -- app离开时间
                       , min(app_arrival_time)                                                 as next_unload_site_scantime        -- 下一站app到达时间
                       , max(weight_arrival_time)                                              as weight_arrival_time              --过磅进站时间
                       , max(weight_departure_time)                                            as weight_departure_time            --过磅出站时间
                       , max(gps_arrival_time)                                                 as app_arrival_time                 ---gps到达时间
                       , max(app_departure_time)                                               as app_departure_time               ---gps离开时间
                       , max(if(start_site_sort_num = shipment_site_cnt,estimate_batch_time,null))   as plan_frequency               --应赶批次
                       , max(if(start_site_sort_num = shipment_site_cnt,actual_batch_time,null)) as  actual_frequency                 -- 实际发车频次
                       -- ,null as forecast_frequency -- 预测参加频次:实际发车+计划在途时长（+1h)
                      -- :todo 计入人工减免时长
                       , if(min(planned_arrival_time) < max(actual_arrival_time), 1, 0)        as is_delay                         -- 是否延误
                       , sum(package_total_weight)                                             as bill_weight                      -- 快件重量
                       , sum(package_inside_charge_weight)                                     as bill_fee_weight                  -- 计费重量
                       , dt
                  from jms_dwd.dwd_wide_tms_road_waybill_dt
                  where dt between date_add('{{ execution_date | cst_ds }}',-7) and '{{ execution_date | cst_ds }}'
                  and trailer_number is not null
                  group by shipment_id,
                           shipment_no,
                           shipment_name,
                           plate_number,
                           trailer_number,
                           roadsection_id,
                           dt
              ) t
     ),

     -- 始发中心
     start_center as (
     select
              shipment_no
             ,cast(sum(package_total_volume) as double) as start_center_out_volume --出港方数
             ,cast(sum(package_total_weight) as double) as start_center_out_weight  --出港重量
             ,count(distinct packagecode) as start_center_out_package_cnt  --出港包数
             ,sum(case when packagecode is not null then 1 else 0 end )  as start_center_out_package_bill_cnt  --出港包内件数
             ,sum(case when packagecode is null then 1 else 0 end )   as start_center_out_bill_cnt --出港单票数
             ,count(1) as start_center_out_total--始发中心出港总票数
      from jms_dwd.dwd_wide_tms_road_waybill_dt
      where roadsection_id=1 and
      dt between date_add('{{ execution_date | cst_ds }}', -7)
      AND '{{ execution_date | cst_ds }}'
      group by
      shipment_no
     ),

     -- 目的中心
     end_center as (
     select
              shipment_no
             ,cast(sum(package_total_volume) as double) as end_center_in_volume --进港方数
             ,cast(sum(package_total_weight) as double) as end_center_in_weight  --进港重量
      from jms_dwd.dwd_wide_tms_road_waybill_dt
      where end_code=end_site_code and
      dt between date_add('{{ execution_date | cst_ds }}', -7)
      AND '{{ execution_date | cst_ds }}'
      group by
      shipment_no
     )
insert overwrite table jms_dws.dws_wide_tms_trailer_road_dt partition (dt)
select
          stop.shipment_id
         ,stop.shipment_no
         ,stop.shipment_name
         ,stop.plate_number
         ,stop.trailer_number
         ,stop.roadsection_id -- 路段编号
         ,stop.roadsection_rank -- 路段排序
         ,cast(null as string) as handling_type
         ,stop.load_start_time -- 装箱开始时间
         ,stop.load_end_time --装箱结束时间
         ,cast(null as string) as run_type
         ,stop.road_maleage -- 规划里程
         ,stop.is_emport
         ,stop.is_over_time
         ,stop.return_sign
         ,stop.loadrate_weight
         ,stop.loadrate_volume
         ,stop.load_site_planned_departure_time -- 计划发车时间
         ,stop.load_site_actual_departure_time -- 实际发车时间
         ,stop.unload_site_planned_arrival_time -- 计划到车时间
         ,stop.unload_site_app_arrival_time -- 实际到车时间
         ,stop.plan_driving_time -- 计划行驶时间
         ,stop.actual_driving_time -- 实际行驶时间
         ,stop.load_site_scantime -- 装车扫描时间
         ,stop.load_site_app_departure_time -- app离开时间
         ,stop.next_unload_site_scantime -- 下一站app到达时间
         ,stop.weight_arrival_time --过磅进站时间
         ,stop.weight_departure_time --过磅出站时间
         ,stop.app_arrival_time ---gps到达时间
         ,stop.app_departure_time ---gps离开时间
         ,plan_frequency -- 计划发车频次


         ,actual_frequency -- 实际发车频次
         ,cast(null as string) as forecast_frequency -- 预测参加频次:实际发车+计划在途时长（+1h)
         -- :todo 计入人工减免时长
         ,stop.is_delay -- 是否延误
         ,stop.bill_weight -- 快件重量
         ,stop.bill_fee_weight -- 计费重量

         -- 任务信息
         ,shipment.shipment_state
         ,shipment.mileage
         ,shipment.vehicleline_type  -- 支线or干线
         -- 任务开始站点
         ,shipment.shipment_start_code
         ,shipment.shipment_start_name
         ,shipment.shipment_plan_start_time -- 任务计划开始时间
         ,shipment.shipment_departure_start_time -- 任务实际开始时间
         ,shipment.shipment_gps_departure_time -- 任务在起始网点的gps开始时间
         -- 任务目的站点
         ,shipment.shipment_end_site_code
         ,shipment.shipment_end_site_name
         ,shipment.shipment_plan_end_time -- 任务计划到车时间
         ,shipment.shipment_departure_end_time -- 任务实际到车时间
         ,shipment.shipment_gps_arrival_time --目的网点的gps到车时间

         -- 始末中心数据
         ,start_center.start_center_out_volume
         ,start_center.start_center_out_weight
         ,start_center.start_center_out_total -- 始发中心出港总件数
         ,start_center.start_center_out_package_cnt -- 始发中心出港包数
         ,start_center.start_center_out_package_bill_cnt -- 始发中心出港包内件
         ,start_center.start_center_out_bill_cnt -- 始发中心出港单票数

         ,end_center.end_center_in_volume
         ,end_center.end_center_in_weight

        --车辆信息
        ,cars.vehicletype_id --车型
        ,cars.vehicletype_name --车型
        ,cars.max_volume --车辆荷载体积
        ,cars.max_weight --车辆荷载重量
        ,cars.carrier_id --承运商ID
        ,cars.carrier_name --承运商全名
        ,cars.driver_name  --主驾驶员
        ,cars.driver1_tel --主驾驶员电话
        ,cars.copilot_driver as driver2 --副驾驶员
        ,cars.copilot_tel as driver2_tel --副驾驶员电话
        ,cars.actual_start_date_type --发车时间类型
        ,cars.actual_end_date_type --到车时间类型
        ,cars.oil --油耗
        ,cars.trailer_car_type --挂车车厢类型
        ,cars.center_one_disp_date --网点一派时间
        ,cars.center_two_disp_date --网点二派时间
        ,cars.center_three_disp_date --网点三派时间
        ,dt
from stop
left join shipment on stop.shipment_no = shipment.shipment_no
                          and stop.trailer_number = shipment.trailer_number
left join start_center on stop.shipment_no = start_center.shipment_no
left join end_center on stop.shipment_no = end_center.shipment_no
left join cars on stop.plate_number = cars.truck_number and stop.shipment_no = cars.shipment_no
;